/******************************************************************************
 * Copyright (C) 2014 ShenZhen YiHua Computer Co., Ltd.
 * All Rights Reserved.
 * 本软件为深圳怡化电脑股份有限公司开发研制。未经本公司正式书面同意，其他任何个人、团体
 * 不得使用、复制、修改或发布本软件.
 *****************************************************************************/
package net.dgg.rz.common.utils;

import net.dgg.iboss.base.util.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class ExcelTool {
    /**
     * *
     *
     * @param filename            保存到客户端的文件名
     * @param title               标题行 例：String[]{"名称","地址"}
     * @param key                 从查询结果List取得的MAP的KEY顺序，需要和title顺序匹配，例：String[]{"name","address"}
     * @param values              结果集
     * @param httpServletResponse
     * @throws IOException
     */
    public static void createN(String filename, String[] title, String[] key,
                               @SuppressWarnings("rawtypes") List<Map> values, HttpServletResponse httpServletResponse) {
        OutputStream servletOutputStream = null;
        try {
//            String filename2 = new String(filename.getBytes("GBK"), "ISO8859-1");
            //servletOutputStream = httpServletResponse.getOutputStream();
            servletOutputStream = new BufferedOutputStream(httpServletResponse.getOutputStream());
            HSSFWorkbook workbook = null;//创建工作簿
//            httpServletResponse.setHeader("Content-disposition", "attachment; filename=" + filename2);
//            httpServletResponse.setContentType("application/x-download");
            httpServletResponse.setContentType("application/octet-stream; charset=UTF-8");
//            httpServletResponse.setContentType("application/octet-stream");
            httpServletResponse.setHeader("Content-Disposition", "attachment; filename=\""
                    + new String(filename.getBytes("GBK"), "ISO8859-1") + "\"");
            workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet();//创建页
            HSSFRow row = null;
            HSSFCell cell = null;
            row = sheet.createRow((short) 0);
            for (int i = 0; title != null && i < title.length; i++) {
                cell = row.createCell(i);
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(new HSSFRichTextString(title[i]));
            }
            @SuppressWarnings("rawtypes")
            Map map = null;
            for (int i = 0; values != null && i < values.size(); i++) {
                row = sheet.createRow(i + 1);
                map = values.get(i);
                for (int i2 = 0; i2 < key.length; i2++) {
                    cell = row.createCell(i2);
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    if (map.get(key[i2]) == null) {
                        cell.setCellValue(new HSSFRichTextString(""));
                    } else {
                        cell.setCellValue(new HSSFRichTextString(map.get(key[i2]).toString()));
                    }
                }
            }
            workbook.write(servletOutputStream);
           /* //获取一个向Response对象写入数据的流,当tomcat服务器进行响应的时候，会将Response中的数据写给浏览器
            String data = "导出成功";
            OutputStream stream = httpServletResponse.getOutputStream();
            stream.write(data.getBytes("UTF-8"));*/
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != servletOutputStream) {
                    servletOutputStream.flush();
                    servletOutputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }

        }
    }

    /**
     * @param cell
     * @return String    返回类型
     * @Title: changeToString
     * @Description: 将Cell对象转换成String类型（导入时调用）
     */
    public static String cellValue(Cell cell) {
        String strCell = "";
        if (cell != null) {
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_FORMULA:
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    strCell = cell.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        Date d = cell.getDateCellValue();
                        DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm");
                        //DateFormat format= new SimpleDateForma;
                        strCell = formater.format(d);
                        //只有日期的转化
                        strCell = strCell.contains("00:00") ? strCell.substring(0, 10) : strCell;
                    } else {
                        strCell = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                case Cell.CELL_TYPE_STRING:
                    strCell = cell.getStringCellValue();
                    break;
                default:
            }
        }
        return strCell.trim();
    }


    /**
     * @param row
     * @param cellIndex
     * @return String    返回类型
     * @Title: cellToString
     * @Description: 根据索引返回行中某个单元格的值
     */
    public static String cellValue(Row row, int cellIndex) {
        return cellValue((Cell) row.getCell(cellIndex));
    }

    /**
     * @param row
     * @param cellIndex
     * @return String    返回类型
     * @Title: cellValueStr
     * @Description: 读取单元格的文本，如果是数字将替换“.0”
     */
    public static String cellValueStr(Row row, int cellIndex) {
        String value = cellValue((Cell) row.getCell(cellIndex));
        if (StringUtils.isNotBlank(value)) {
            value = value.replace(".0", "");
        }
        return value;
    }

    /**
     * @param row
     * @param cellIndex
     * @param title
     * @return String    返回类型
     * @Title: cellTitleCheck
     * @Description: 检查Excel文件的某个列的列名是否与传入的title一致
     */
    public static String cellTitleCheck(Row row, int cellIndex, String title) {
        String result = "";
        String s = ExcelTool.cellValue(row, cellIndex);
        if (!StringUtils.isNotBlank(s) || !s.equals(title)) {
            result = "第" + (cellIndex + 1) + "列必须为[" + title + "]列<br/>";
        }
        return result;
    }


    /**
     * @param row        行对象
     * @param cellIndex  列索引
     * @param objErr     错误提示接受数组
     * @param errorIndex 错误提示保存位置
     * @param title      字段名
     * @return boolean    返回类型
     * @Title: cellValueBlank
     * @Description: Excel单元格非空检查
     */
    public static boolean cellValueNotBlank(Row row, int cellIndex, Object objErr[], int errorIndex, String title) {
        boolean result = true;
        String s = ExcelTool.cellValue(row, cellIndex);
        objErr[cellIndex] = s.replace(".0", "");
        if (StringUtils.isNotBlank(s)) {
        } else {
            result = false;
            objErr[errorIndex] = objErr[errorIndex] + title + "不能为空\r\n";
        }
        return result;
    }

    /**
     * @param row        行对象
     * @param cellIndex  列索引
     * @param objErr     错误提示接受数组
     * @param errorIndex 错误提示保存位置
     * @param title      字段名
     * @return boolean    返回类型
     * @Title: cellValueBlank
     * @Description: Excel单元格非空检查(字符不包含小数)
     */
    public static boolean cellTextValueNotBlank(Row row, int cellIndex, Object objErr[], int errorIndex, String title) {
        boolean result = true;
        ;
        String s = ExcelTool.cellValue(row, cellIndex);
        objErr[cellIndex] = s.replace(".0", "");
        if (StringUtils.isNotBlank(s)) {
        } else {
            result = false;
            objErr[errorIndex] = objErr[errorIndex] + title + "不能为空\r\n";
        }
        return result;
    }

    /**
     * @param cellValue
     * @param dbValue
     * @param objErr
     * @param errorIndex
     * @param title
     * @return boolean    返回类型
     * @Title: cellValueEqualsDbValue
     * @Description: 比较两个字符串的值，并记录错误信息
     */
    public static boolean cellValueEqualsDbValue(String cellValue, String dbValue, Object objErr[], int errorIndex, String title) {
        boolean result = true;
        if (!cellValue.equals(dbValue)) {
            result = false;
            objErr[errorIndex] = objErr[errorIndex] + title + "与数据库中数据不一致！\r\n";
        }
        return result;
    }

    /**
     * @param row        行对象
     * @param cellIndex  列索引
     * @param objErr     错误提示接受数组
     * @param errorIndex 错误提示保存位置
     * @param title      字段名
     * @return boolean    返回类型
     * @Title: cellValueNotBlankNumber
     * @Description: Excel单元格非空、电子邮件地址检查
     */
    public static boolean cellValueNotBlankEmail(Row row, int cellIndex, Object objErr[], int errorIndex, String title) {
        boolean result = true;
        String s = ExcelTool.cellValue(row, cellIndex);
        objErr[cellIndex] = s;
        if (StringUtils.isNotBlank(s)) {
            if (StringUtils.isValidEmail(s)) {
            } else {
                result = false;
                objErr[errorIndex] = objErr[errorIndex] + "必须为邮箱地址的" + title + "\r\n";
            }
        } else {
            result = false;
            objErr[errorIndex] = objErr[errorIndex] + title + "不能为空\r\n";
        }
        return result;
    }

    /**
     * @param row        行对象
     * @param cellIndex  列索引
     * @param objErr     错误提示接受数组
     * @param errorIndex 错误提示保存位置
     * @param title      字段名
     * @return boolean    返回类型
     * @Title: cellValueNotBlankMobile
     * @Description: Excel单元格非空、手机号码地址检查
     */
    public static boolean cellValueNotBlankMobile(Row row, int cellIndex, Object objErr[], int errorIndex, String title) {
        boolean result = true;
        String s = ExcelTool.cellValue(row, cellIndex);
        BigDecimal db = new BigDecimal(s);//避免电话号码读出来为科学计数法
        s = db.toPlainString();
        objErr[cellIndex] = s + "";
        if (StringUtils.isNotBlank(s)) {
            if (StringUtils.isValidMobileNO(s)) {
            } else {
                result = false;
                objErr[errorIndex] = objErr[errorIndex] + "必须为手机号码的" + title + "\r\n";
            }
        } else {
            result = false;
            objErr[errorIndex] = objErr[errorIndex] + title + "不能为空\r\n";
        }
        return result;
    }

    /**
     * 导出
     * @param list   需要导出的数据list
     * @param sheetName  文件名
     * @param title  表头数组
     * @param keyArray  list里面对象的key值
     * @param widths  每个单元格的宽度
     * @param styleArr 每个单元格位置  -1 靠左  0 居中  1 靠右
     */

    public static void execlExport(List list,String sheetName,String[] title,String[] keyArray,int[] widths,int[] styleArr){
        //第一步创建workbook
        HSSFWorkbook wb = new HSSFWorkbook();
        //第二步创建sheet
        HSSFSheet sheet = wb.createSheet(sheetName);
        //设置宽度
        PoiUtil.setColumnWidth(sheet, widths);
        sheet.setDefaultRowHeight((short)(360));
        HSSFRow row_0 = sheet.createRow(0);
        for(int i=0; i<title.length; i++) {
            Cell cell_0_i = row_0.createCell(i);
            cell_0_i.setCellType(Cell.CELL_TYPE_STRING);
            cell_0_i.setCellStyle(PoiUtil.getCellStyle(wb, 0, true, HSSFColor.GREY_25_PERCENT.index));
            cell_0_i.setCellValue(title[i]);
        }
        CellStyle[] styleArray = new CellStyle[styleArr.length];
        for(int k=0 ; k<styleArr.length;k++){
            styleArray[k] = PoiUtil.getCellStyle(wb, styleArr[k], true);

        }

        for(int i=0; i<list.size(); i++) {
            Row row_i = sheet.createRow(i+1);
            for(int j=0; j<keyArray.length; j++) {
                Cell cell_i_j = row_i.createCell(j);
                cell_i_j.setCellType(Cell.CELL_TYPE_STRING);
                cell_i_j.setCellStyle(styleArray[j]);
                Object value = ObjectUtil.getFieldValue(list.get(i), keyArray[j]);
                if(value == null) {
                    cell_i_j.setCellValue("");
                }else if(value instanceof BigDecimal){
                    BigDecimal amt = (BigDecimal) value;
                    cell_i_j.setCellValue(DecimalFormatUtil.toString(amt, ""));
                }else if(value instanceof Date){
                    Date amt = (Date) value;
                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    cell_i_j.setCellValue(simpleDateFormat.format(amt));
                }else {
                    cell_i_j.setCellValue(value.toString());
                }
            }
        }


        String fileName = sheetName+ DateUtils.getNowDateString("yyyyMMddHHmmss")+".xls";
        try {
            HttpServletResponse response = ServletUtil.getResponse();
            OutputStream out = null;
            response.addHeader("Content-Disposition", "attachment;fileName="+ URLEncoder.encode(fileName,"UTF-8"));
            response.setCharacterEncoding("utf-8");
            response.setContentType("multipart/form-data");
            out = response.getOutputStream();
            wb.write(out);
            out.flush();
        }catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * 导出有json的那种
     * @param list   需要导出的数据list
     * @param sheetName  文件名
     * @param title  表头数组
     * @param keyArray  list里面对象的key值
     * @param widths  每个单元格的宽度
     * @param styleArr 每个单元格位置  -1 靠左  0 居中  1 靠右
     */

    public static void execlExportJson(List list,String sheetName,String[] title,String[] keyArray,int[] widths,int[] styleArr){
        //第一步创建workbook
        HSSFWorkbook wb = new HSSFWorkbook();
        //第二步创建sheet
        HSSFSheet sheet = wb.createSheet(sheetName);
        //设置宽度
        PoiUtil.setColumnWidth(sheet, widths);
        sheet.setDefaultRowHeight((short)(360));
        HSSFRow row_0 = sheet.createRow(0);
        for(int i=0; i<title.length; i++) {
            Cell cell_0_i = row_0.createCell(i);
            cell_0_i.setCellType(Cell.CELL_TYPE_STRING);
            cell_0_i.setCellStyle(PoiUtil.getCellStyle(wb, 0, true, HSSFColor.GREY_25_PERCENT.index));
            cell_0_i.setCellValue(title[i]);
        }
        CellStyle[] styleArray = new CellStyle[styleArr.length];
        for(int k=0 ; k<styleArr.length;k++){
            styleArray[k] = PoiUtil.getCellStyle(wb, styleArr[k], true);

        }

        for(int i=0; i<list.size(); i++) {
            Row row_i = sheet.createRow(i+1);
            for(int j=0; j<keyArray.length; j++) {
                Cell cell_i_j = row_i.createCell(j);
                cell_i_j.setCellType(Cell.CELL_TYPE_STRING);
                cell_i_j.setCellStyle(styleArray[j]);
                Object value = ObjectUtil.getFieldJson(list.get(i), keyArray[j]);
                if(value == null) {
                    cell_i_j.setCellValue("");
                }else if(value instanceof BigDecimal){
                    BigDecimal amt = (BigDecimal) value;
                    //银行家进位法
                    BigDecimal as = amt.setScale(2, BigDecimal.ROUND_HALF_UP);
                    cell_i_j.setCellValue(DecimalFormatUtil.toString(as, ""));
                }else if(value instanceof Date){
                    Date amt = (Date) value;
                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    cell_i_j.setCellValue(simpleDateFormat.format(amt));
                }else {
                    cell_i_j.setCellValue(value.toString());
                }
            }
        }
        String fileName = sheetName+ DateUtils.getNowDateString("yyyyMMddHHmmss")+".xls";
        try {
            HttpServletResponse response = ServletUtil.getResponse();
            OutputStream out = null;
            response.addHeader("Content-Disposition", "attachment;fileName="+ URLEncoder.encode(fileName,"UTF-8"));
            response.setCharacterEncoding("utf-8");
            response.setContentType("multipart/form-data");
            out = response.getOutputStream();
            wb.write(out);
            out.flush();
        }catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * 导出Map数据
     * @param list   需要导出的数据list
     * @param sheetName  文件名
     * @param title  表头数组
     * @param keyArray  list里面对象的key值
     * @param widths  每个单元格的宽度
     * @param styleArr 每个单元格位置  -1 靠左  0 居中  1 靠右
     */
    public static void execlExportMap(List<Map<String,Object>> list,String sheetName,String[] title,String[] keyArray,int[] widths,int[] styleArr){
        //第一步创建workbook
        HSSFWorkbook wb = new HSSFWorkbook();
        //第二步创建sheet
        HSSFSheet sheet = wb.createSheet(sheetName);
        //设置宽度
        PoiUtil.setColumnWidth(sheet, widths);
        sheet.setDefaultRowHeight((short)(360));
        HSSFRow row_0 = sheet.createRow(0);
        for(int i=0; i<title.length; i++) {
            Cell cell_0_i = row_0.createCell(i);
            cell_0_i.setCellType(Cell.CELL_TYPE_STRING);
            cell_0_i.setCellStyle(PoiUtil.getCellStyle(wb, 0, true, HSSFColor.GREY_25_PERCENT.index));
            cell_0_i.setCellValue(title[i]);
        }
        CellStyle[] styleArray = new CellStyle[styleArr.length];
        for(int k=0 ; k<styleArr.length;k++){
            styleArray[k] = PoiUtil.getCellStyle(wb, styleArr[k], true);

        }
        for(int i=0; i<list.size(); i++) {
            Row row_i = sheet.createRow(i+1);
            for(int j=0; j<keyArray.length; j++) {
                Cell cell_i_j = row_i.createCell(j);
                cell_i_j.setCellType(Cell.CELL_TYPE_STRING);
                cell_i_j.setCellStyle(styleArray[j]);
                Map<String,Object>  dataMap= list.get(i);
                Object value = dataMap.get(""+keyArray[j]+"");
                if(value == null) {
                    cell_i_j.setCellValue("");
                }else if(value instanceof BigDecimal  && ((BigDecimal) value).intValue()!=0 ){
                    BigDecimal amt = (BigDecimal) value;
                    //银行家进位法
                    BigDecimal as = amt.setScale(2, BigDecimal.ROUND_HALF_UP);
                    cell_i_j.setCellValue(DecimalFormatUtil.toString(as, ""));
                }else if(value instanceof Date){
                    Date amt = (Date) value;
                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    cell_i_j.setCellValue(simpleDateFormat.format(amt));
                }else {
                    cell_i_j.setCellValue(value.toString());
                }
            }
        }
        String fileName = sheetName+ DateUtils.getNowDateString("yyyyMMddHHmmss")+".xls";
        try {
            HttpServletResponse response = ServletUtil.getResponse();
            OutputStream out = null;
            response.addHeader("Content-Disposition", "attachment;fileName="+ URLEncoder.encode(fileName,"UTF-8"));
            response.setCharacterEncoding("utf-8");
            response.setContentType("multipart/form-data");
            out = response.getOutputStream();
            wb.write(out);
            out.flush();
        }catch (Exception e) {
            e.printStackTrace();
        }
    }


}
